<?php

function EJGetJourneyEventWithBalance($jid) {
    $journeyEventSql = "select Event from mapjourneyfinanceevent where Journey=" . $jid;
    $result = array();
    EJReadTable($journeyEventSql, function($row) use(&$result) {
        $eid = $row["Event"];
        $eventInfo = EJGetEventInfo(null, $eid);
        if (sizeof($eventInfo) <= 0) {
            W3LogWarning("Event ID of journey is not existed: " . $eid);
            return;
        }

        $eventBillSql = "select " .
                      "bill.Amount as amount, bill.Datetime as datetime, bill.Currency as currency" .
                      " from " .
                      "bill" .
                      " where " .
                      "ID in (select Bill from mapbillfinanceevent where Event =" . $eid . ")";
        $amount = EJSumCurrency($eventBillSql);
        $balance = sprintf("%01.2f", floatval($eventInfo["Budget"]) - $amount);
        array_push($result, array("Name" => $eventInfo["Name"], "Balance" => $balance));
    });
    return $result;
}

function EJGetJourneyTravelers($jid) {
    $sql = "select " .
         "person.Name as name, person.FID as family" .
         " from " .
         "person, mapjourneyperson" .
         " where " .
         "mapjourneyperson.Person = person.ID" .
         " and " .
         "mapjourneyperson.Journey=" . $jid;
    $result = array();
    EJReadTable($sql, function ($row) use (&$result) {
        $result[$row["name"]] = $row["family"];
    });

    return $result;
}

function EJIsJourneyInFamily($jid, $fid) {
    $travelers = EJGetJourneyTravelers($jid);
    if (sizeof($travelers) <= 0) {
        W3LogWarning("Journey traveler is empty for " . $jid);
        return false;
    }
    foreach ($travelers as $personName => $personFID) {
        if ($personFID != $fid) {
            return false;
        }
    }

    return true;
}

function EJGetJourney(&$journeyParams) {
    $aid = "aidJourney";
    return EJExecuteWithAuthenticatedFamily($aid, $journeyParams, function ($fid, $aid, &$journeyParams) {
        $paramOffset = 1; # The first one is the whole string from reg match
        $startDate = $journeyParams[W3GetAPIParamIndex($aid, "from") + $paramOffset];
        $endDate = $journeyParams[W3GetAPIParamIndex($aid, "to") + $paramOffset];

        $sql = "select " .
             "journey.Datetime as datetime, journey.Name as name, journey.Note as note, journey.ID as id" .
             " from " .
             "journey" .
             " where " .
             "journey.Datetime >= '" . $startDate . "' and journey.Datetime <= '" . $endDate . "'" .
             " order by journey.Datetime asc";

        $result = "{" . W3CreateSuccessfulPartialResult() . "," . W3MakeString(w3ApiResultData) . ":[";
        EJReadTable($sql, function ($row) use (&$result, $aid, $fid) {
            $jid = $row["id"];
            if (!EJIsJourneyInFamily($jid, $fid)) {
                return;
            }

            $travelers = EJGetJourneyTravelers($jid);
            $travelerStr = "";
            foreach ($travelers as $personName => $personFID) {
                $travelerStr .= $personName . ", ";
            }
            $travelerStr = rtrim($travelerStr, ", ");

            $eventName = "";
            $eventBalance = "";
            $eventInfos = EJGetJourneyEventWithBalance($jid);
            foreach ($eventInfos as $event) {
                $eventName .= $event["Name"] . ", ";
                $eventBalance .= $event["Balance"] . ", ";
            }
            $eventName = rtrim($eventName, ", ");
            $eventBalance = rtrim($eventBalance, ", ");

            $apiDef = W3GetAPIDef($aid);
            $columns = $apiDef[w3ApiResult][w3ApiResultData];
            $result .= "{";
            foreach ($columns as $value) {
                if ($value[w3ApiDataValue] == "balance") {
                    $result .= W3MakeString($value[w3ApiDataValue]) . ":" . W3MakeString($eventBalance) . ",";
                } else if ($value[w3ApiDataValue] == "event") {
                    $result .= W3MakeString($value[w3ApiDataValue]) . ":" . W3MakeString($eventName) . ",";
                } else if ($value[w3ApiDataValue] == "traveler") {
                    $result .= W3MakeString($value[w3ApiDataValue]) . ":" . W3MakeString($travelerStr) . ",";
                } else {
                    $result .= W3MakeString($value[w3ApiDataValue]) . ":" . W3MakeString($row[$value[w3ApiDataValue]]) . ",";
                }
            }
            $result = rtrim($result, ",") . "},";
        });
        $result = rtrim($result, ",") . "]}";

        return $result;
    });
}

function EJAddJourney(&$parameters) {
    $aid = "aidAddJourney";
    return EJExecuteWithAuthenticatedFamily($aid, $parameters, function ($fid, $aid, &$parameters) {
        $paramOffset = 1; # The first one is alway whole string from reg match
        $eventStr = EJDecodeURLString($parameters[W3GetAPIParamIndex($aid, "event") + $paramOffset]);
        $travelerStr = $parameters[W3GetAPIParamIndex($aid, "traveler") + $paramOffset];

        $eventIDs = EJConvertEventStringToIDs($eventStr, $fid);

        $travelerIDs = explode(",", $travelerStr);
        if (sizeof($travelerIDs) <= 0) {
            W3LogWarning("No traveler of journey");
            return W3CreateFailedResult();
        }
        for ($i = 0; $i < sizeof($travelerIDs); ++$i) {
            $travelerIDs[$i] = trim($travelerIDs[$i]);
            if (!EJIsPersonInFamily($travelerIDs[$i], $fid)) {
                W3LogWarning("Traveler of journey is not in same family");
                return W3CreateFailedResult();
            }
        }

        if (EJInsertJourney($parameters)) {
            if (EJMapJourneyToPerson($travelerIDs) and EJMapJourneyToFinanceEvent($eventIDs)) {
                return W3CreateSuccessfulResult();
            }
        }

        return W3CreateFailedResult();
    });
}

function EJAddJourneyPlace(&$parameters) {
    $aid = "aidAddJourneyPlace";
    return EJExecuteWithAuthenticatedFamily($aid, $parameters, function ($fid, $aid, &$parameters) {
        // Check whether journey is valid for family
        $paramOffset = 1; # The first one is alway whole string from reg match
        $journeyID = $parameters[W3GetAPIParamIndex($aid, "jid") + $paramOffset];
        if (!EJIsJourneyInFamily($journeyID, $fid)) {
            W3LogWarning("Journey is not for operator family: " . $journeyID);
            return W3CreateFailedResult();
        }

        $placeID = EJInsertJourneyPlace($parameters, $aid);
        if ($placeID < 0) {
            W3LogWarning("Failed to get journey place ID");
            return W3CreateFailedResult();
        }

        if (EJInsertJourneyNote($parameters, $placeID)) {
            return W3CreateSuccessfulResult();
        }

        return W3CreateFailedResult();
    });
}

function EJGetJourneyPlace(&$journeyParams) {
    $aid = "aidJourneyPlace";
    return EJExecuteWithAuthenticatedFamily($aid, $journeyParams, function ($fid, $aid, &$journeyParams) {
        $paramOffset = 1; # The first one is the whole string from reg match
        $jid = $journeyParams[W3GetAPIParamIndex($aid, "jid") + $paramOffset];

        if (!EJIsJourneyInFamily($jid, $fid)) {
            W3LogWarning("Try to query journy not in family!");
            return W3CreateFailedResult();
        }

        $sql = "select " .
             "journeynote.Datetime as datetime, journeynote.Remark as remark, journeynote.Note as note, " .
             "journeyplace.Name as name, journeyplace.Latitude as latitude, journeyplace.Longitude as longitude" .
             " from " .
             "journeynote, journeyplace" .
             " where " .
             "journeynote.Place=journeyplace.ID" .
             " and " .
             "journeynote.Journey=" . $jid .
             " order by journeynote.Datetime asc";

        return EJReadMultiResultFromTable($aid, $sql);
    });
}

function EJGetAllPlace(&$params) {
    $aid = "aidAllPlace";
    return EJExecuteWithAuthenticatedFamily($aid, $params, function ($fid, $aid, &$params) {
        $sqlJourney = "select " .
                    "journey.ID" .
                    " from " .
                    "journey, mapjourneyperson, person" .
                    " where " .
                    "journey.ID=mapjourneyperson.Journey" .
                    " and " .
                    "mapjourneyperson.Person=person.ID" .
                    " and " .
                    "person.FID=" . $fid;
        $sqlPlace = "select " .
             "journeynote.Datetime as datetime, journeynote.Remark as remark, journeynote.Note as note, " .
             "journeyplace.Name as name, journeyplace.Latitude as latitude, journeyplace.Longitude as longitude, journeyplace.ID as placeid" .
             " from " .
             "journeynote, journeyplace" .
             " where " .
             "journeynote.Place=journeyplace.ID" .
             " and " .
             "journeynote.Journey in (" . $sqlJourney . ")" .
             " order by journeynote.Datetime asc";

        $result = "{" . W3CreateSuccessfulPartialResult() . "," . W3MakeString(w3ApiResultData) . ":[";
        $places = array();
        EJReadTable($sqlPlace, function ($row) use (&$places, &$result, $aid) {
            if (in_array($row["placeid"], $places)) {
                return;
            }
            array_push($places, $row["placeid"]);

            $apiDef = W3GetAPIDef($aid);
            $columns = $apiDef[w3ApiResult][w3ApiResultData];
            $result .= "{";
            foreach ($columns as $value) {
                $resultForColumn = $row[$value[w3ApiDataValue]];
                $result .= W3MakeString($value[w3ApiDataValue]) . ":" . W3MakeString($resultForColumn) . ",";
            }
            $result = rtrim($result, ",") . "},";
        });
        $result = rtrim($result, ",") . "]}";

        return $result;
    });
}

function EJAddPOI(&$parameters) {
    $aid = "aidAddPOI";
    return EJExecuteWithAuthenticatedFamily($aid, $parameters, function ($fid, $aid, &$parameters) {
        $placeID = EJInsertJourneyPlace($parameters, $aid);
        if ($placeID < 0) {
            W3LogWarning("Failed to get POI place ID");
            return W3CreateFailedResult();
        }

        if (EJInsertPOI($parameters, $placeID, $fid)) {
            return W3CreateSuccessfulResult();
        }

        return W3CreateFailedResult();
    });
}

function EJGetAllPOI(&$params) {
    $aid = "aidAllPOI";
    return EJExecuteWithAuthenticatedFamily($aid, $params, function ($fid, $aid, &$params) {
        $paramOffset = 1; # The first one is the whole string from reg match
        $jid = $params[W3GetAPIParamIndex($aid, "jid") + $paramOffset];

        $sql = "select " .
             "poi.ID as poiid, poi.Note as note, " .
             "journeyplace.Name as name, journeyplace.Latitude as latitude, journeyplace.Longitude as longitude" .
             " from " .
             "poi, journeyplace" .
             " where " .
             "poi.Place=journeyplace.ID" .
             " and " .
             "poi.FID=" . $fid .
             " order by poi.ID asc";

        return EJReadMultiResultFromTable($aid, $sql);
    });
}

function EJAddPOIToJourney(&$parameters) {
    $aid = "aidAddPOIToJourney";
    return EJExecuteWithAuthenticatedFamily($aid, $parameters, function ($fid, $aid, &$parameters) {
        $paramOffset = 1; # The first one is the whole string from reg match
        $poiID = $parameters[W3GetAPIParamIndex($aid, "poi") + $paramOffset];

        $sql = "select journeyplace.ID as id from poi, journeyplace where poi.Place=journeyplace.ID and poi.ID=" . $poiID .
             " and poi.FID=" . $fid;
        $placeID = NULL;
        EJReadTable($sql, function ($row) use (&$placeID) {
            $placeID = $row["id"];
        });
        if ($placeID == NULL) {
            W3LogWarning("Failed to get POI place when add POI to journey");
            return W3CreateFailedResult();
        }

        // Mock "aidAddJourneyPlace"
        $jid = $parameters[W3GetAPIParamIndex($aid, "journey") + $paramOffset];
        $datetime = $parameters[W3GetAPIParamIndex($aid, "datetime") + $paramOffset];
        $remark = $parameters[W3GetAPIParamIndex($aid, "remark") + $paramOffset];
        $note = $parameters[W3GetAPIParamIndex($aid, "note") + $paramOffset]; // It's mock and no decode here
        $mockParams = array(
            "Whold regex string",
            $jid,
            "name",
            $datetime,
            "latitude",
            "longitude",
            $remark,
            $note,
            $session
        );
        if (!EJInsertJourneyNote($mockParams, $placeID)) {
            return W3CreateFailedResult();
        }

        // Remove POI because it's already in journey
        $sql = "delete from poi where ID=" . $poiID;
        if (!EJExecuteSQL($sql)) {
            return W3CreateFailedResult();
        }

        return W3CreateSuccessfulResult();
    });
}

function EJInsertJourney(&$journeyParams) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert journey");
        return false;
    }

    $columns = array ("Name", "Datetime", "Note");
    $size = sizeof($columns);

    $uselessParamCount = 4; # Match string, session, etc. And "Event" will be check later
    $paramOffset = 1; # 0 is for the whole match string in reg
    if (sizeof($journeyParams) - $uselessParamCount != $size) {
        W3LogWarning("No enough fields for journey insert: require " .
                   strval($size) .
                   " but actual is " .
                   strval(sizeof($journeyParams) - $paramOffset));
        return false;
    }

    $aid = "aidAddJourney";
    $journeyNote = EJDecodeURLString($journeyParams[W3GetAPIParamIndex($aid, "note") + $paramOffset]);
    $journeyName = EJDecodeURLString($journeyParams[W3GetAPIParamIndex($aid, "name") + $paramOffset]);
    $values = array(W3MakeSingleQuotString($journeyName),
                    W3MakeSingleQuotString($journeyParams[W3GetAPIParamIndex($aid, "datetime") + $paramOffset]),
                    W3MakeSingleQuotString($journeyNote));

    $sql = "insert into journey (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";

    if (!EJExecuteSQL($sql)) {
        W3LogWarning("Execute journey insert SQL failed");
        return false;
    }

    return true;
}

function EJInsertJourneyPlace(&$placeParams, $aid) {
    $placeID = -1;

    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert journey place");
        return $placeID;
    }

    $paramOffset = 1; # 0 is for the whole match string in reg
    $name = $placeParams[W3GetAPIParamIndex($aid, "name") + $paramOffset]; # This name will be Decode by client
    $latitude = $placeParams[W3GetAPIParamIndex($aid, "latitude") + $paramOffset];
    $longitude = $placeParams[W3GetAPIParamIndex($aid, "longitude") + $paramOffset];

    // First check whether the place is there already

    $querySql = "select ID as id from journeyplace where " .
              "Latitude=" . $latitude .
              " and Longitude=" . $longitude;
    EJReadTable($querySql, function ($row) use (&$placeID) {
        $placeID = $row["id"];
    });

    if ($placeID >= 0) {
        return $placeID;
    }

    // Then if not place existed, add a new one

    $columns = array ("Name", "Latitude", "Longitude");
    $values = array(W3MakeString($name), $latitude, $longitude);
    $insertSql = "insert into journeyplace (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";
    if (!EJExecuteSQL($insertSql)) {
        W3LogWarning("Execute journey place insert SQL failed");
        return $placeID;
    }

    // Return the new place ID

    $maxIDSql = "select max(ID) from journeyplace";
    EJReadTable($maxIDSql, function ($row) use (&$placeID) {
        $placeID = $row["max(ID)"];
    });

    return $placeID;
}

function EJInsertJourneyNote(&$noteParams, $placeID) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert journey note");
        return false;
    }

    $currentTime = time();
    $timestamp = strval($currentTime / 3600 % 24) . ":"
               . strval($currentTime / 60 % 60) . ":"
               . strval($currentTime % 60);

    $aid = "aidAddJourneyPlace";
    $paramOffset = 1; # The first one is alway whole string from reg match
    $journeyID = $noteParams[W3GetAPIParamIndex($aid, "jid") + $paramOffset];
    $datetime = $noteParams[W3GetAPIParamIndex($aid, "datetime") + $paramOffset] . " " . $timestamp;
    $remark = $noteParams[W3GetAPIParamIndex($aid, "remark") + $paramOffset];
    $note = EJDecodeURLString($noteParams[W3GetAPIParamIndex($aid, "note") + $paramOffset]);

    $columns = array ("Journey", "Place", "Datetime", "Remark", "Note");
    $values = array($journeyID, $placeID, W3MakeString($datetime), $remark, W3MakeString($note));
    $insertSql = "insert into journeynote (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";
    if (!EJExecuteSQL($insertSql)) {
        W3LogWarning("Execute journey note insert SQL failed");
        return false;
    }

    return true;
}

function EJInsertPOI(&$noteParams, $placeID, $fid) {
    if (!EJConnectDB()) {
        W3LogWarning("No DB connection when insert POI");
        return false;
    }

    $aid = "aidAddPOI";
    $paramOffset = 1; # The first one is alway whole string from reg match
    $note = EJDecodeURLString($noteParams[W3GetAPIParamIndex($aid, "note") + $paramOffset]);

    $columns = array ("FID", "Place", "Note");
    $values = array($fid, $placeID, W3MakeString($note));
    $insertSql = "insert into poi (" . implode(",", $columns) . ") values (" . implode("," , $values) . ")";
    if (!EJExecuteSQL($insertSql)) {
        W3LogWarning("Execute POI insert SQL failed");
        return false;
    }

    return true;
}

function EJGetLatestJourney($op) {
    $maxIDSql = "select max(ID) from journey";
    EJReadTable($maxIDSql, function ($row) use ($op) {
        if (!EJConnectDB()) {
            W3LogWarning("No DB connection when get latest journey");
            return;
        }

        $journeyID = $row["max(ID)"];
        if (trim($journeyID) == "") {
            return;
        }

        $op($journeyID);
    });
}

function EJMapJourneyToPerson($persons) {
    $result = true;

    EJGetLatestJourney(function ($jid) use ($persons, &$result) {
        foreach ($persons as $pid) {
            $insertSql = "insert into mapjourneyperson (Journey, Person) values (" . $jid . ", " . $pid . ")";
            if (!EJExecuteSQL($insertSql)) {
                W3LogWarning("Execute journey person insert SQL failed");
                $result = false;
                return;
            }
        }
    });

    return $result;
}

function EJMapJourneyToFinanceEvent($events) {
    $result = true;

    EJGetLatestJourney(function ($jid) use ($events, &$result) {
        foreach ($events as $eid) {
            $insertSql = "insert into mapjourneyfinanceevent (Journey, Event) values (" . $jid . ", " . $eid . ")";
            if (!EJExecuteSQL($insertSql)) {
                W3LogWarning("Execute journey finance event insert SQL failed");
                $result = false;
                return;
            }
        }
    });

    return $result;
}

function EJCreateJourneyTravelerSelectionPanel($pid) {
    // Get travelers from login user family
    $session = W3GetSession();
    $fid = EJGetLoginFamily($session);
    if ($fid == null) {
        W3LogWarning("Failed to get family ID to create journey traveler selection panel");
        return "<p>Login first please!</p>";
    }
    $ui = "";
    $personInfo = EJGetPersonInfoInFamily($fid);
    $index = 1;
    foreach ($personInfo as $person) {
        $subId = strval($pid) . "Selection" . strval($index++);
        $travelerName = strval($person["Name"]);
        $travelerID = strval($person["ID"]);
        $event = "onclick=\"EJOnJourneyTravelerClicked('" . strval($pid) . "','" . $subId . "')\"";
        $ui .= "<input id='" . $subId . "' type='checkbox' value='" . $travelerID . "' " . $event . ">" .
            $travelerName .
            "</input>";
    }

    return $ui;
}

 ?>
